﻿using System.Data;
using System.Data.OleDb;
using System.IO;

namespace Opet
{
    public class Excell
    {
        private DataTable ExcellToDataTable(FileInfo xlsFile, bool firsRowCol)
        {
            DataTable dt = new DataTable();
            using (OleDbConnection connection = new System.Data.OleDb.OleDbConnection())
            {
                if (System.IO.Path.GetExtension(xlsFile.Name) == ".xlsx")
                {
                    if (firsRowCol)
                    {
                        connection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + xlsFile.FullName + ";Extended Properties='Excel 12.0;IMEX=1;HDR=No'";
                    }
                    else
                    {
                        connection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + xlsFile.FullName + ";Extended Properties='Excel 12.0;IMEX=1;'";
                    }
                }
                else
                    if (firsRowCol)
                    {
                        connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + xlsFile.FullName + ";Extended Properties='Excel 8.0;IMEX=1;HDR=No'";
                    }
                    else
                    {
                        connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + xlsFile.FullName + ";Extended Properties='Excel 8.0;IMEX=1;'";
                    }

                connection.Open();

                DataTable dte = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                string tblName = dte.Rows[0].ItemArray[2].ToString();
                using (OleDbDataAdapter dataAdapter1 = new OleDbDataAdapter(string.Format("select * from [{0}]", tblName), connection))
                {
                    dataAdapter1.Fill(dt);
                    if (dt.Rows.Count == 0)
                    {
                        tblName = dte.Rows[1].ItemArray[2].ToString();
                        dataAdapter1.SelectCommand.CommandText = string.Format("select * from [{0}]", tblName);
                        dataAdapter1.Fill(dt);
                    }
                }
            }
            return dt;
        }
    }
}
